<?php
include "otamu_config.php";
include("helpers/FusionCharts/Code/PHP/Includes/FusionCharts.php");
include("helpers/FusionCharts/Code/PHP/Includes/FC_Colors.php");

define("_VIEWPATH_","views/reporting/");
$layout = "reporting_layout";

if (!isset($_REQUEST['act'])) $_REQUEST['act'] = ''; 
if (!isset($show_range)) $show_range = 0;
if (!isset($the_date)) $the_date = Date('d-m-Y');

if ( !isset($year) || (''==$year) ) $year = Date('Y');
if ( !isset($month) || (''==$month) ) $month = Date('m');
if ( !isset($day) || (''==$day) ) $day = Date('d');

$act = $_REQUEST['act'];
switch ($act) {

	case "raw_water_resources" :
		$group_id = '9';
		$sql = "SELECT date_part('month',the_date) as the_month,the_value FROM statistics WHERE sub_item_id='$si_id' 
				 AND date_part('year',the_date)='$the_year'";
		//$res = pg_query($sql);
		//while ($dat = pg_fetch_object($res)) {
		//	$vals[$si_id][] = $dat->the_value;
		//}
		$year = Date("Y");
		$states = getStates();
		$items = getItemsOfGroup($group_id); // ( 10 => 'River', 11 => 'Dam')
		$sums = getChildSumOfItemByState('4','1','2010');
		//print_r($states);exit;
		break;

	
	case "sek_ikut_dun_agensi" :
		if (empty($_REQUEST['pkod'])) {
			$R_kod_parlimen = "148";
			$R_kod_negeri = "01";
		}
		else {
			$R_kod_parlimen = $_REQUEST['pkod'];
		}

		//cari nama parlimen & nama negeri
		$sa = "select * from tkparlimen p INNER JOIN tknegeri n ON n.kodnegeri=p.negeri where kodparlimen='$R_kod_parlimen'";
		$ra = pg_query($sa);
		while($dat = pg_fetch_object($ra)){
			$parlimen = $dat->parlimen;
			$negeri   = $dat->negeri;
			$R_kod_negeri = $dat->kodnegeri;
		}
		$R_kod_negeri = sprintf("%02d",$R_kod_negeri);

		//cari dun
		$sa = "SELECT distinct(kod_dun),dun FROM spk_sekolah_statistik WHERE kod_kawasan_parlimen = '$R_kod_parlimen' ORDER BY dun";
		$ra = pg_query($sa);
		$duns = array();
		while($da = pg_fetch_object($ra)){
			if ($da->dun =='') {
				$duns[(string)$da->kod_dun] = "Tiada Maklumat";	
			}
			else {
				$duns[(string)$da->kod_dun] = $da->dun;
			}
			
		}
		//print_r($duns);exit;
		if ($_REQUEST['o'] == "kelas") {
			$table = "spk_kelas_statistik";
		}
		else {
			$table = "spk_sekolah_statistik";
		}
		$s = "SELECT kod_dun,kod_kategori_institusi,sum(bil) as bil from $table 
		       WHERE kod_kawasan_parlimen='$R_kod_parlimen' AND aktif <> '0'
			   GROUP BY kod_dun,kod_kategori_institusi	 
			   ORDER BY kod_dun,kod_kategori_institusi
			   ";
		$murid_res = pg_query($s);

		$hash = $total_hash = array();
		while ($dat = pg_fetch_object($murid_res)) {
			$kod_dun = $dat->kod_dun;
			$kod_agensi = $dat->kod_kategori_institusi;

			$hash4[$kod_dun][$kod_agensi] = $dat->bil;
			$total_vertical4[$kod_agensi] += $dat->bil;
		}

		$total_horizontal = array();
		foreach ($duns as $kod_dun => $v) {
			foreach ($agensis as $kod_agensi => $ag) {
				$total_horizontal4[$kod_dun] += $hash4[$kod_dun][$kod_agensi];
			}
		}

		$total_all = 0;
		foreach ($total_horizontal4 as $k => $v) {
			$total_all += $v;
		}
		//print_r($total_horizontal4);exit;

			//$strXML will be used to store the entire XML document generated
			//Generate the graph element 
			$yAxisName = "Bilangan Sekolah";
			if ($_REQUEST['o']=="kelas") {
				$yAxisName = "Bilangan Kelas";
			}
			$strXML = "<graph xAxisName='Dun' yAxisName='". $yAxisName ."' caption='' subCaption='' decimalPrecision='0' rotateNames='1' numDivLines='2' numberPrefix='' numberSuffix=' Orang' showValues='0' formatNumberScale='0'>";
			
			//x axis(negeri)
			$strXML .= "<categories>";
			foreach ($duns as $kod_dun => $dun) {
				$strXML .= "<category name='" . $dun . "' />";
			}
			$strXML .= "</categories>";
			
			//$strXML .= '<dataset seriesName="2004" color="AFD8F8" showValues="0">';
			foreach ($agensis as $kod_agensi => $agensi) {
				//Generate <dataset seriesName="2004" color="AFD8F8" showValues="0"> 
				//$strXML .= "<set name='" . $av . "' value='" . $total_horizontal[$agnc_kod] . "' color='".getFCColor() ."' />";
				$strXML .= "<dataset seriesName='" . $agensi . "' color='" .getFCColor() ."'  showValues='0'>";
					foreach ($duns as $kod_dun => $dun) {
						$v = $hash4[$kod_dun][$kod_agensi];
						$strXML .= "<set value='" . $v . "'/>";
					}
			$strXML .= "</dataset>";	
			}
			
			//Finally, close <graph> element
			$strXML .= "</graph>";
			$FC_graph_pie = renderChart("helpers/FusionCharts/Charts/FCF_StackedColumn3D.swf", "", $strXML, "FactorySum", 800, 550);

		break;

	case "sek_ikut_parlimen_agensi" :
		if (empty($_REQUEST['nkod'])) {
			$R_kod_negeri = "01";
		}
		else {
			$R_kod_negeri = $_REQUEST['nkod'];
		}
		$R_kod_negeri = sprintf("%02d",$R_kod_negeri);

		//cari parlimen
		$sa = "SELECT distinct(kod_kawasan_parlimen),parlimen FROM spk_sekolah_statistik WHERE kod_negeri = '$R_kod_negeri' ORDER BY parlimen";
		$ra = pg_query($sa);
		$parlimens = array();
		while($da = pg_fetch_object($ra)){
			$parlimens[(string)$da->kod_kawasan_parlimen] = $da->parlimen;
		}
		//print_r($parlimens);exit;

		if ($_REQUEST['o'] == "kelas") {
			$table = "spk_kelas_statistik";
		}
		else {
			$table = "spk_sekolah_statistik";
		}
		$s = "SELECT kod_kawasan_parlimen,kod_kategori_institusi,sum(bil) as bil from $table 
		       WHERE kod_negeri='$R_kod_negeri' AND aktif <> '0'
			   GROUP BY kod_kawasan_parlimen,kod_kategori_institusi	 
			   ORDER BY kod_kawasan_parlimen,kod_kategori_institusi
			   ";
		$murid_res = pg_query($s);

		$hash = $total_hash = array();
		while ($dat = pg_fetch_object($murid_res)) {
			$kod_parlimen = $dat->kod_kawasan_parlimen;
			$kod_agensi = $dat->kod_kategori_institusi;

			$hash4[$kod_parlimen][$kod_agensi] = $dat->bil;
			$total_vertical4[$kod_agensi] += $dat->bil;
		}

		$total_horizontal = array();
		foreach ($parlimens as $kod_parlimen => $v) {
			foreach ($agensis as $kod_agensi => $ag) {
				$total_horizontal4[$kod_parlimen] += $hash4[$kod_parlimen][$kod_agensi];
			}
		}

		$total_all = 0;
		foreach ($total_horizontal4 as $k => $v) {
			$total_all += $v;
		}
		//print_r($total_horizontal4);exit;

			$yAxisName = "Bilangan Sekolah";
			if ($_REQUEST['o']=="kelas") {
				$yAxisName = "Bilangan Kelas";
			}
			//$strXML will be used to store the entire XML document generated
			//Generate the graph element 
			$strXML = "<graph xAxisName='Parlimen' yAxisName='". $yAxisName ."' caption='' subCaption='' decimalPrecision='0' rotateNames='1' numDivLines='2' numberPrefix='' numberSuffix='' showValues='0' formatNumberScale='0'>";
			
			//x axis(negeri)
			$strXML .= "<categories>";
			foreach ($parlimens as $kod_parlimen => $parlimen) {
				$strXML .= "<category name='" . $parlimen . "' />";
			}
			$strXML .= "</categories>";
			
			//$strXML .= '<dataset seriesName="2004" color="AFD8F8" showValues="0">';
			foreach ($agensis as $kod_agensi => $agensi) {
				//Generate <dataset seriesName="2004" color="AFD8F8" showValues="0"> 
				//$strXML .= "<set name='" . $av . "' value='" . $total_horizontal[$agnc_kod] . "' color='".getFCColor() ."' />";
				$strXML .= "<dataset seriesName='" . $agensi . "' color='" .getFCColor() ."'  showValues='0'>";
					foreach ($parlimens as $kod_parlimen => $parlimen) {
						$v = $hash4[$kod_parlimen][$kod_agensi];
						$strXML .= "<set value='" . $v . "'/>";
					}
			$strXML .= "</dataset>";	
			}
			
			//Finally, close <graph> element
			$strXML .= "</graph>";
			$FC_graph_pie = renderChart("helpers/FusionCharts/Charts/FCF_StackedColumn3D.swf", "", $strXML, "FactorySum", 800, 550);

		break;


	case "senarai_laporan" :
		break;

	case "sek_ikut_negeri" :
		/*if (!isset($output) || ($output=='')) $output = 'listing';
		$date_to_find = dmy_date($the_date);
		$sql = "SELECT * FROM vw_outing WHERE tarikh_keluar='$date_to_find'";
		$students = pg_query($sql);*/
		break;

	case "list" :
		if (!isset($output) || ($output=='')) $output = 'listing';
		include _VIEWPATH_."$layout.php";
		break;


		
	default :
		include _VIEWPATH_."$layout.php";
		break;
		
}
include _VIEWPATH_."$layout.php";

?>